﻿using IDAdmin.Lib.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;

namespace IDAdmin.Lib.DataLayer
{
    public class LevelDisAccLossIncreNewWebDB
    {
        public const string hostName = "222.255.177.27";
        public const int port = 9823;
        List<ServerDTO> gameServerList;
        public LevelDisAccLossIncreNewWebDB()
        {
            gameServerList = WebDB_N.ListServer().Select(s => new ServerDTO() { GameZone = s.gamezone.ToString(), Name = s.tablezone, StartSeverDate = s.created.ToString("yyyy-MM-dd HH:mm:ss") }).ToList();
        }
        public List<Au> getNau(DateTime date)
        {
            List<Au> nauList = new List<Au>();
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                string commandText = "select DISTINCT(account) as account,gamezone  from nau where datetime between '{0}' and '{1}'";
                commandText = string.Format(commandText, date.ToString("yyyy-MM-dd") + " 00:00:00", date.ToString("yyyy-MM-dd") + " 23:59:59");
                SqlCommand cmd = new SqlCommand(commandText, connection);
                cmd.CommandTimeout = 3600;
                SqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    nauList.Add(new Au() { Account = dataReader["account"].ToString().ToLower(), GameZone = dataReader["gamezone"].ToString() });
                }
                dataReader.Close();
            }
            return nauList;
        }

        public List<Au> getAuSecondToEightDay(DateTime date)
        {
            List<Au> auSecondToEightDay = new List<Au>();
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                string commandText = "select DISTINCT(account),gamezone from au where adatetime between '{0}' and '{1}'";
                commandText = string.Format(commandText, date.AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00", date.AddDays(7).ToString("yyyy-MM-dd") + " 23:59:59");
                SqlCommand cmd = new SqlCommand(commandText, connection);
                cmd.CommandTimeout = 3600;
                SqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    auSecondToEightDay.Add(new Au() { Account = dataReader["account"].ToString().ToLower(), GameZone = dataReader["gamezone"].ToString() });
                }
                dataReader.Close();
            }
            return auSecondToEightDay;
        }
        public void insertCALIN(DateTime date)
        {
            List<Au> nauList = getNau(date);
            List<Au> auSecondToEightDayList = getAuSecondToEightDay(date);
            List<Au> accLossIcreNewList = new List<Au>();
            foreach (Au account in nauList)
            {
                var haslogin = auSecondToEightDayList.Where(w => w.Account.ToLower().Equals(account.Account) && w.GameZone.Equals(account.GameZone)).FirstOrDefault();
                if (haslogin == null)
                {
                    accLossIcreNewList.Add(account);
                }
            }
            LevelWebDB.delete_CALIN_Table(date.ToString("yyyy-MM-dd") + " 00:00:00", date.ToString("yyyy-MM-dd") + " 23:59:59");
            //var fsfsaf=accLossIcreNewList.Select(s => s.Account).Distinct().ToList();
            //int casfsafsa=fsfsaf.Count;
            foreach (Au account in accLossIcreNewList)
            {
                string url = "http://{0}:{1}/getCharidName?gosuAccount={2}&game=38&zone={3}";
                url = string.Format(url, hostName, port, account.Account, account.GameZone);
                string result = HttpHelper.HttpSocket(url, 26);
                if (string.IsNullOrEmpty(result) == false)
                {
                    if (result.Substring(0, 1).Equals("0"))
                    {
                        JavaScriptSerializer jss = new JavaScriptSerializer();
                        CharidName cin = jss.Deserialize<CharidName>(result.Substring(2, result.IndexOf("}", result.IndexOf("name")) - 1));
                        LevelWebDB.insert_CALIN_Table(date, account.Account, cin.charid, account.GameZone);
                    }
                    else
                    {

                    }
                }
            }
        }

        public List<LevelAndPeopleNum> getStats(DateTime date,out List<string> outLevelList)
        {
            List<LevelAndPeopleNum> lvAndPeoNumList = new List<LevelAndPeopleNum>();
            List<string> levelList = new List<string>();
            Dictionary<string, List<LevelAndPeopleNum>> lvAndPeoNumDic = new Dictionary<string, List<LevelAndPeopleNum>>();
            try
            {
                foreach (var server in gameServerList)
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select COUNT(tb.charid) as amount,TB.lv as level from (select le.charid,max(level) as lv from Level{0} as le join CharLossIcreNew as calin on le.charid=calin.charid   WHERE calin.GameZone={1} and le.datetime between '{2}' and '{3}' and calin.datetime between '{4}' and '{5}' group by le.charid) as tb group by tb.lv";

                        string commandText2 = string.Format(commandText, server.Name, server.GameZone, server.StartSeverDate, date.ToString("yyyy-MM-dd") + " 23:59:59", date.ToString("yyyy-MM-dd") + " 00:0:00", date.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText2;
                        try
                        {
                            SqlDataReader dataReader = command.ExecuteReader();
                            List<LevelAndPeopleNum> list = new List<LevelAndPeopleNum>();
                            while (dataReader.Read())
                            {
                                LevelAndPeopleNum lap = new LevelAndPeopleNum();
                                lap.Level = dataReader.GetInt64(1).ToString();
                                levelList.Add(lap.Level);
                                lap.PeopleNum = dataReader.GetValue(0).ToString();
                                list.Add(lap);
                            }
                            dataReader.Close();
                            lvAndPeoNumDic.Add(server.Name, list);
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
                levelList = levelList.Distinct().ToList();
                outLevelList = levelList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            List<LevelAndPeopleNum> levAndPeopListInterme = lvAndPeoNumDic.SelectMany(s => s.Value).ToList();
            foreach (var level in levelList)
            {
                LevelAndPeopleNum lapn = new LevelAndPeopleNum();
                lapn.PeopleNum = levAndPeopListInterme.Where(w => w.Level.Equals(level)).Sum(s => int.Parse(s.PeopleNum)).ToString();
                lapn.Level = level;
                lvAndPeoNumList.Add(lapn);
            }
            return lvAndPeoNumList;
        }

        public void insertStatsData(List<LevelAndPeopleNum> data, DateTime date)
        {
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {//sp_Insert_PremiumLogin

                SqlCommand deleteCommand = new SqlCommand("sp_delete_LevelCalin", conn);
                deleteCommand.CommandTimeout = 3600;
                deleteCommand.CommandType = CommandType.StoredProcedure;
                deleteCommand.Parameters.AddWithValue("@fromDT", date.ToString("yyyy-MM-dd") + " 00:00:00");
                deleteCommand.Parameters.AddWithValue("@toDT", date.ToString("yyyy-MM-dd") + " 23:59:59");
                deleteCommand.ExecuteNonQuery();
            }
            foreach (LevelAndPeopleNum item in data)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand("sp_Insert_LevelCalin", conn);
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@datetime", date);
                        command.Parameters.AddWithValue("@Level", item.Level);
                        command.Parameters.AddWithValue("@peoplenum", item.PeopleNum);
                        command.ExecuteNonQuery();
                    }
                }
                catch(Exception ex)
                {

                }
            }
        }
        public List<LevelAndPeopleNum> getFastStatsData(string from, string to,List<string> levelList)
        {
            List<LevelAndPeopleNum> data = new List<LevelAndPeopleNum>();
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_Select_LevelCalin", conn);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@fromDT", from);
                command.Parameters.AddWithValue("@toDT", to);
                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    LevelAndPeopleNum lapn = new LevelAndPeopleNum();
                    //lapn.Date = dataReader.GetDateTime(0).ToString("dd/MM/yyyy");
                    lapn.Level = dataReader.GetInt64(1).ToString();
                    levelList.Add(lapn.Level);
                    lapn.PeopleNum = dataReader.GetInt64(2).ToString();
                    data.Add(lapn);
                }
                dataReader.Close();
            }
            return data;
        }
        /*public void get(DateTime date)
        {
            List<string> acccLossIncreNew = new List<string>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string cmdText = "select DISTINCT(n.account) from nau n where n.datetime between '{0}' and '{1}' and n.account not in (select n.account from nau n,au a where N.account=A.account AND n.datetime between '{2}' and '{3}' and a.adatetime between '{4}' and '{5}')";
                    cmdText = string.Format(cmdText, date.ToString("yyyy-MM-dd") + " 00:00:00", date.ToString("yyyy-MM-dd") + " 23:59:59", date.ToString("yyyy-MM-dd") + " 00:00:00", date.ToString("yyyy-MM-dd") + " 23:59:59", date.AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00", date.AddDays(7).ToString("yyyy-MM-dd") + " 23:59:59");
                    SqlCommand cmd = new SqlCommand(cmdText, connection);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        acccLossIncreNew.Add(reader.GetValue(0).ToString());
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {

            }
            foreach (string account in acccLossIncreNew)
            {
                foreach (ServerDTO server in gameServerList)
                {
                    string url = "http://{0}:{1}/getCharidName?gosuAccount={2}&game=38&zone={3}";
                    url = string.Format(url, hostName, port, account, server.GameZone);
                    string result = HttpHelper.HttpSocket(url, 26);
                    if (string.IsNullOrEmpty(result) == false)
                    {
                        if (result.Substring(0, 1).Equals("0"))
                        {
                            JavaScriptSerializer jss = new JavaScriptSerializer();
                            CharidName cin = jss.Deserialize<CharidName>(result.Substring(2, result.IndexOf("}", result.IndexOf("name")) - 1));
                        }
                        else
                        {

                        }
                    }
                }
            }
        }*/
    }
}
